﻿using System.Data;
using System.Data.SqlClient;
using System.Text;
using H2.NET.Common;
using H2.NET.DBUtility;
using H2.NET.Web.Workflow.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace H2.NET.Web.Workflow.Services
{
    public class GroupService
    {
        public GroupService()
        {
            DbHelperSQL.connectionString = PubConstant.GetConnectionString("bpmDB");
        }

        public void Maintain(Groups model, string actiontype)
        {
            switch (actiontype)
            {
                case "add":
                    {
                        const string sql = " INSERT INTO GROUPS(GNAME,MEMBER,MEMBERTYPE,GROUPTYPE,WEIGHT,SEQ,PROFILE,MEMBERID,RIGHTS) " +
                                           " VALUES (@GNAME,@MEMBER,@MEMBERTYPE,@GROUPTYPE,@WEIGHT,@SEQ,@PROFILE,@MEMBERID,@RIGHTS)";
                        DbHelperSQL.ExecuteSql(sql,
                                new SqlParameter("@GNAME", model.Gname),
                                new SqlParameter("@MEMBER", model.Member),
                                new SqlParameter("@MEMBERTYPE", model.MemberType),
                                new SqlParameter("@GROUPTYPE", model.GroupType),
                                new SqlParameter("@WEIGHT", 0),
                                new SqlParameter("@SEQ", 0),
                                new SqlParameter("@PROFILE", ""),
                                new SqlParameter("@MEMBERID", 0),
                                new SqlParameter("@RIGHTS", ""));
                    }
                    break;
                case "update":
                    {
                        const string sql = "UPDATE [GROUPS] SET " +
                                           "[MEMBER] = @MEMBER,[MEMBERTYPE] = @MEMBERTYPE,[GROUPTYPE] = @GROUPTYPE " +
                                           "WHERE [GNAME] = @GNAME AND [MEMBER] = @OLDMEMBER";
                        DbHelperSQL.ExecuteSql(sql,
                                new SqlParameter("@GNAME", model.Gname),
                                new SqlParameter("@MEMBER", model.Member),
                                new SqlParameter("@OLDMEMBER", model.OldMember),
                                new SqlParameter("@MEMBERTYPE", model.MemberType),
                                new SqlParameter("@GROUPTYPE", model.GroupType));
                    }
                    break;
                case "delete":
                    {
                        const string sql = "DELETE FROM [GROUPS] WHERE [GNAME] = @GNAME AND [MEMBER] = @OLDMEMBER";
                        DbHelperSQL.ExecuteSql(sql,
                            new SqlParameter("@GNAME", model.Gname),
                            new SqlParameter("@OLDMEMBER", model.OldMember));
                        break;
                    }
            }
        }

        /// <summary>
        /// 查询所有的组名
        /// </summary>
        /// <returns></returns>
        public List<string> FindGroupNamesByKey(string key)
        {
            const string sql = "SELECT GNAME FROM [GROUPS] GROUP BY GNAME HAVING GNAME LIKE @key";
            var dt = DbHelperSQL.Query(sql, new SqlParameter("@key", "%" + key + "%")).Tables[0];
            return (from DataRow row in dt.Rows select row["gname"].ToString()).ToList();
        }

        public List<Groups> FindGroupsByGname(string gname)
        {
            const string sql = "SELECT * FROM [GROUPS] WHERE GNAME = @gname";
            var dt = DbHelperSQL.Query(sql, new SqlParameter("@gname", gname)).Tables[0];
            var groups = new List<Groups>();
            foreach (DataRow row in dt.Rows)
            {
                var group = new Groups();
                group.Gname = row["gname"].ToString();
                group.Member = row["Member"].ToString();
                group.MemberType = Utils.StrToInt(row["MemberType"].ToString(), 0);
                group.GroupType = Utils.StrToInt(row["GroupType"].ToString(), 0);
                groups.Add(group);
            }
            return groups;
        }

        public bool IsChannelAll(string name)
        {
            const string sql = "SELECT COUNT(1) FROM t_Group_Member WHERE grp_m_fk_grp_no = 'GRP_SMD_018' and grp_m_type_name = '人员' AND grp_m_type_v = @grp_m_type_v";
            var result = DbHelperSQL.GetSingle(sql, new SqlParameter("@grp_m_type_v", name));
            if (result == null) return false;
            return Utils.StrToInt(result.ToString(), 0) > 0;
        }
    }
}